package com.example.contextcube.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.example.contextcube.model.Word;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 词典数据库助手类
 * 用于管理SQLite数据库的访问
 */
public class DictionaryDatabaseHelper extends SQLiteOpenHelper {
    private static final String TAG = "DictionaryDBHelper";
    
    // 数据库名称和版本
    private static final String DATABASE_NAME = "dictionary_lite.db";
    private static final int DATABASE_VERSION = 1;

    // 单词表
    private static final String TABLE_WORDS = "words";

    // 最近查询表 - 独立的数据库文件
    private static final String RECENT_DB_NAME = "recent_queries.db";
    private static final int RECENT_DB_VERSION = 1;
    private static final String TABLE_RECENT_QUERIES = "recent_queries";
    private static final String COLUMN_WORD = "word";
    private static final String COLUMN_PHONETIC = "phonetic";
    private static final String COLUMN_DEFINITION = "definition";
    private static final String COLUMN_TRANSLATION = "translation";
    private static final String COLUMN_QUERY_TIME = "query_time";
    private static final int MAX_RECENT_QUERIES = 20;
    
    // 收藏单词表
    private static final String FAVORITES_DB_NAME = "favorites.db";
    private static final int FAVORITES_DB_VERSION = 1;
    private static final String TABLE_FAVORITES = "favorites";
    private static final String COLUMN_FAVORITE_TIME = "favorite_time";
    
    // 最近查询数据库助手类
    private RecentQueriesDBHelper recentQueriesHelper;
    
    // 收藏单词数据库助手类
    private FavoritesDBHelper favoritesDBHelper;

    private final Context context;
    private String databasePath;

    public DictionaryDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
        
        // 获取数据库在设备上的存储路径
        this.databasePath = context.getDatabasePath(DATABASE_NAME).getPath();
        
        // 创建最近查询数据库助手
        this.recentQueriesHelper = new RecentQueriesDBHelper(context);
        
        // 创建收藏单词数据库助手
        this.favoritesDBHelper = new FavoritesDBHelper(context);
        
        // 确保数据库存在
        try {
            createDatabaseIfNotExists();
        } catch (IOException e) {
            Log.e(TAG, "Error creating database", e);
        }
    }

    /**
     * 如果数据库不存在，从assets目录复制到应用数据目录
     */
    private void createDatabaseIfNotExists() throws IOException {
        boolean dbExists = checkDatabaseExists();
        
        if (!dbExists) {
            // 通过调用getReadableDatabase触发创建空数据库
            this.getReadableDatabase();
            this.close();
            
            // 复制assets中的数据库文件
            copyDatabaseFromAssets();
        }
    }

    /**
     * 检查数据库文件是否存在
     */
    private boolean checkDatabaseExists() {
        File dbFile = new File(databasePath);
        return dbFile.exists();
    }

    /**
     * 从assets目录复制数据库文件
     */
    private void copyDatabaseFromAssets() throws IOException {
        InputStream inputStream = context.getAssets().open(DATABASE_NAME);
        
        // 确保目录存在
        File dbFile = new File(databasePath);
        File parentDir = dbFile.getParentFile();
        if (parentDir != null && !parentDir.exists()) {
            parentDir.mkdirs();
        }
        
        // 复制文件
        OutputStream outputStream = new FileOutputStream(databasePath);
        byte[] buffer = new byte[1024];
        int length;
        
        while ((length = inputStream.read(buffer)) > 0) {
            outputStream.write(buffer, 0, length);
        }
        
        outputStream.flush();
        outputStream.close();
        inputStream.close();
        
        Log.i(TAG, "Database copied successfully");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 数据库结构已经在assets中的文件中预先定义，无需在此创建
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    /**
     * 根据前缀搜索单词
     */
    public List<Word> searchWordsByPrefix(String prefix) {
        List<Word> words = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            // 构建查询SQL，支持前缀匹配
            String query = "SELECT word, phonetic, definition, translation FROM " + TABLE_WORDS + 
                           " WHERE word LIKE ? || '%' ORDER BY LENGTH(word) LIMIT 30";
            
            Cursor cursor = db.rawQuery(query, new String[]{prefix.toLowerCase()});
            
            if (cursor.moveToFirst()) {
                do {
                    Word word = new Word();
                    word.setWord(cursor.getString(0));
                    word.setPhonetic(cursor.getString(1));
                    word.setDefinition(cursor.getString(2));
                    word.setTranslation(cursor.getString(3));
                    words.add(word);
                } while (cursor.moveToNext());
            }
            
            cursor.close();
        } catch (Exception e) {
            Log.e(TAG, "Error searching words", e);
        }
        
        return words;
    }
    
    /**
     * 获取单个单词的完整信息
     */
    public Word getWord(String wordText) {
        SQLiteDatabase db = this.getReadableDatabase();
        Word word = null;
        
        try {
            String query = "SELECT word, phonetic, definition, translation FROM " + TABLE_WORDS + 
                           " WHERE word = ? LIMIT 1";
            
            Cursor cursor = db.rawQuery(query, new String[]{wordText.toLowerCase()});
            
            if (cursor.moveToFirst()) {
                word = new Word();
                word.setWord(cursor.getString(0));
                word.setPhonetic(cursor.getString(1));
                word.setDefinition(cursor.getString(2));
                word.setTranslation(cursor.getString(3));
            }
            
            cursor.close();
        } catch (Exception e) {
            Log.e(TAG, "Error getting word details", e);
        }
        
        return word;
    }

    /**
     * 添加单词到最近查询
     */
    public void addToRecentQueries(Word word) {
        if (word == null || word.getWord() == null) {
            return;
        }
        
        recentQueriesHelper.addRecentQuery(word);
    }
    
    /**
     * 获取最近查询列表
     */
    public List<Word> getRecentQueries() {
        return recentQueriesHelper.getRecentQueries();
    }
    
    /**
     * 清空最近查询
     */
    public void clearRecentQueries() {
        recentQueriesHelper.clearRecentQueries();
    }
    
    /**
     * 添加或移除收藏单词
     * @param word 单词对象
     * @param isFavorite 是否收藏
     * @return 操作是否成功
     */
    public boolean toggleFavoriteWord(Word word, boolean isFavorite) {
        if (word == null || word.getWord() == null) {
            return false;
        }
        
        if (isFavorite) {
            return favoritesDBHelper.addFavorite(word);
        } else {
            return favoritesDBHelper.removeFavorite(word.getWord());
        }
    }
    
    /**
     * 检查单词是否被收藏
     * @param wordText 单词文本
     * @return 是否被收藏
     */
    public boolean isWordFavorited(String wordText) {
        return favoritesDBHelper.isFavorite(wordText);
    }
    
    /**
     * 获取所有收藏的单词
     * @param sortByAlphabet 是否按字母排序，否则按收藏时间排序
     * @return 收藏的单词列表
     */
    public List<Word> getFavoriteWords(boolean sortByAlphabet) {
        return favoritesDBHelper.getAllFavorites(sortByAlphabet);
    }
    
    /**
     * 获取所有收藏单词的字符串列表
     */
    public List<String> getAllFavoritedWordStrings() {
        return favoritesDBHelper.getAllFavoriteWordStrings();
    }

    /**
     * 根据单词字符串切换收藏状态
     */
    public boolean toggleFavoriteWordByString(String word, boolean isFavorite) {
        if (TextUtils.isEmpty(word)) return false;
        
        try {
            if (isFavorite) {
                // 查询单词信息
                Word wordObj = findWordByString(word);
                if (wordObj != null) {
                    // 如果在词典中找到了单词，添加到收藏
                    return favoritesDBHelper.addFavorite(wordObj);
                } else {
                    // 如果词典中不存在该单词，创建一个简单的单词对象并添加到收藏
                    // 这解决了某些特殊单词如"OK"无法被收藏的问题
                    Word newWord = new Word();
                    newWord.setWord(word);
                    newWord.setPhonetic(""); // 默认值，不影响显示
                    newWord.setDefinition(""); // 默认值
                    newWord.setTranslation(""); // 默认值
                    return favoritesDBHelper.addFavorite(newWord);
                }
            } else {
                // 从收藏移除
                return favoritesDBHelper.removeFavorite(word);
            }
        } catch (Exception e) {
            Log.e(TAG, "切换收藏状态出错: " + word, e);
            return false;
        }
    }
    
    /**
     * 根据单词字符串查询单词对象
     */
    private Word findWordByString(String wordStr) {
        if (wordStr == null) {
            Log.e(TAG, "findWordByString: 传入的单词为null");
            return null;
        }
        
        Log.d(TAG, "findWordByString: 开始查询单词: " + wordStr);
        SQLiteDatabase db = getReadableDatabase();
        Word word = null;
        
        try {
            Cursor cursor = db.query(
                    TABLE_WORDS,
                    new String[]{COLUMN_WORD, COLUMN_PHONETIC, COLUMN_DEFINITION, COLUMN_TRANSLATION},
                    COLUMN_WORD + " = ?",
                    new String[]{wordStr},
                    null, null, null
            );
            
            if (cursor != null) {
                int count = cursor.getCount();
                Log.d(TAG, "findWordByString: 查询结果数量: " + count);
                
                if (cursor.moveToFirst()) {
                    word = new Word();
                    word.setWord(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_WORD)));
                    word.setPhonetic(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PHONETIC)));
                    word.setDefinition(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_DEFINITION)));
                    word.setTranslation(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TRANSLATION)));
                    Log.d(TAG, "findWordByString: 找到单词: " + word.getWord());
                } else {
                    Log.d(TAG, "findWordByString: 数据库中不存在此单词: " + wordStr);
                }
                cursor.close();
            } else {
                Log.d(TAG, "findWordByString: 查询结果为null");
            }
        } catch (Exception e) {
            Log.e(TAG, "findWordByString: 查询单词出错: " + wordStr, e);
        }
        
        return word;
    }
    
    /**
     * 最近查询数据库助手，使用独立的数据库文件
     */
    private static class RecentQueriesDBHelper extends SQLiteOpenHelper {
        
        public RecentQueriesDBHelper(Context context) {
            super(context, RECENT_DB_NAME, null, RECENT_DB_VERSION);
        }
        
        @Override
        public void onCreate(SQLiteDatabase db) {
            // 创建最近查询表
            String createTableRecentQueries = "CREATE TABLE IF NOT EXISTS " + TABLE_RECENT_QUERIES + " (" +
                    COLUMN_WORD + " TEXT PRIMARY KEY, " +
                    COLUMN_PHONETIC + " TEXT, " +
                    COLUMN_DEFINITION + " TEXT, " +
                    COLUMN_TRANSLATION + " TEXT, " +
                    COLUMN_QUERY_TIME + " INTEGER)";
            
            db.execSQL(createTableRecentQueries);
        }
        
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // 暂不处理升级逻辑
        }
        
        /**
         * 添加最近查询
         */
        public void addRecentQuery(Word word) {
            SQLiteDatabase db = this.getWritableDatabase();
            
            try {
                db.beginTransaction();
                
                // 首先删除已存在的相同单词(如果有)
                db.delete(TABLE_RECENT_QUERIES, COLUMN_WORD + " = ?", new String[] { word.getWord() });
                
                // 插入新的查询记录
                ContentValues values = new ContentValues();
                values.put(COLUMN_WORD, word.getWord());
                values.put(COLUMN_PHONETIC, word.getPhonetic());
                values.put(COLUMN_DEFINITION, word.getDefinition());
                values.put(COLUMN_TRANSLATION, word.getTranslation());
                values.put(COLUMN_QUERY_TIME, System.currentTimeMillis()); // 当前时间
                
                db.insert(TABLE_RECENT_QUERIES, null, values);
                
                // 删除多余的记录，只保留最新的MAX_RECENT_QUERIES条
                String deleteSql = "DELETE FROM " + TABLE_RECENT_QUERIES + 
                        " WHERE " + COLUMN_WORD + " NOT IN (" +
                        "SELECT " + COLUMN_WORD + " FROM " + TABLE_RECENT_QUERIES + 
                        " ORDER BY " + COLUMN_QUERY_TIME + " DESC LIMIT " + MAX_RECENT_QUERIES + ")";
                
                db.execSQL(deleteSql);
                
                db.setTransactionSuccessful();
                Log.d(TAG, "成功添加到最近查询: " + word.getWord());
            } catch (Exception e) {
                Log.e(TAG, "Error adding to recent queries: " + e.getMessage());
            } finally {
                db.endTransaction();
            }
        }
        
        /**
         * 获取最近查询列表
         */
        @SuppressLint("Range")
        public List<Word> getRecentQueries() {
            List<Word> wordList = new ArrayList<>();
            SQLiteDatabase db = this.getReadableDatabase();
            
            // 确保表存在
            onCreate(db);
            
            try {
                String query = "SELECT * FROM " + TABLE_RECENT_QUERIES + 
                              " ORDER BY " + COLUMN_QUERY_TIME + " DESC";
                
                Cursor cursor = db.rawQuery(query, null);
                
                if (cursor.moveToFirst()) {
                    do {
                        Word word = new Word();
                        word.setWord(cursor.getString(cursor.getColumnIndex(COLUMN_WORD)));
                        word.setPhonetic(cursor.getString(cursor.getColumnIndex(COLUMN_PHONETIC)));
                        word.setDefinition(cursor.getString(cursor.getColumnIndex(COLUMN_DEFINITION)));
                        word.setTranslation(cursor.getString(cursor.getColumnIndex(COLUMN_TRANSLATION)));
                        
                        wordList.add(word);
                    } while (cursor.moveToNext());
                }
                
                cursor.close();
                Log.d(TAG, "获取到最近查询数量: " + wordList.size());
            } catch (Exception e) {
                Log.e(TAG, "Error getting recent queries: " + e.getMessage());
            }
            
            return wordList;
        }
        
        /**
         * 清空最近查询
         */
        public void clearRecentQueries() {
            SQLiteDatabase db = this.getWritableDatabase();
            db.delete(TABLE_RECENT_QUERIES, null, null);
        }
    }

    /**
     * 收藏单词数据库助手类
     */
    private static class FavoritesDBHelper extends SQLiteOpenHelper {
        
        public FavoritesDBHelper(Context context) {
            super(context, FAVORITES_DB_NAME, null, FAVORITES_DB_VERSION);
        }
        
        @Override
        public void onCreate(SQLiteDatabase db) {
            // 创建收藏单词表
            String createTableFavorites = "CREATE TABLE IF NOT EXISTS " + TABLE_FAVORITES + " (" +
                    COLUMN_WORD + " TEXT PRIMARY KEY, " +
                    COLUMN_PHONETIC + " TEXT, " +
                    COLUMN_DEFINITION + " TEXT, " +
                    COLUMN_TRANSLATION + " TEXT, " +
                    COLUMN_FAVORITE_TIME + " INTEGER)";
            
            db.execSQL(createTableFavorites);
        }
        
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // 暂不处理升级逻辑
        }
        
        /**
         * 添加收藏单词
         */
        public boolean addFavorite(Word word) {
            SQLiteDatabase db = this.getWritableDatabase();
            try {
                ContentValues values = new ContentValues();
                values.put(COLUMN_WORD, word.getWord().toLowerCase());
                values.put(COLUMN_PHONETIC, word.getPhonetic());
                values.put(COLUMN_DEFINITION, word.getDefinition());
                values.put(COLUMN_TRANSLATION, word.getTranslation());
                values.put(COLUMN_FAVORITE_TIME, System.currentTimeMillis());
                
                // 插入数据，如果已存在则替换
                long result = db.insertWithOnConflict(TABLE_FAVORITES, null, values, 
                        SQLiteDatabase.CONFLICT_REPLACE);
                
                return result != -1;
            } catch (Exception e) {
                Log.e(TAG, "Error adding favorite word", e);
                return false;
            }
        }
        
        /**
         * 移除收藏单词
         */
        public boolean removeFavorite(String wordText) {
            SQLiteDatabase db = this.getWritableDatabase();
            try {
                // 执行删除操作
                db.delete(TABLE_FAVORITES, COLUMN_WORD + " = ?", 
                        new String[]{wordText.toLowerCase()});
                
                // 检查执行后该单词是否存在于收藏表中
                Cursor cursor = db.query(TABLE_FAVORITES, 
                        new String[]{COLUMN_WORD}, 
                        COLUMN_WORD + " = ?",
                        new String[]{wordText.toLowerCase()},
                        null, null, null);
                
                boolean wordNotExists = (cursor == null || cursor.getCount() == 0);
                if (cursor != null) {
                    cursor.close();
                }
                
                // 只要单词不在收藏表中，就视为操作成功
                return wordNotExists;
            } catch (Exception e) {
                Log.e(TAG, "Error removing favorite word", e);
                return false;
            }
        }
        
        /**
         * 检查单词是否被收藏
         */
        public boolean isFavorite(String wordText) {
            SQLiteDatabase db = this.getReadableDatabase();
            try {
                String query = "SELECT " + COLUMN_WORD + " FROM " + TABLE_FAVORITES + 
                        " WHERE " + COLUMN_WORD + " = ? LIMIT 1";
                
                Cursor cursor = db.rawQuery(query, new String[]{wordText.toLowerCase()});
                boolean isFavorite = cursor.getCount() > 0;
                cursor.close();
                
                return isFavorite;
            } catch (Exception e) {
                Log.e(TAG, "Error checking if word is favorited", e);
                return false;
            }
        }
        
        /**
         * 获取所有收藏单词
         */
        public List<Word> getAllFavorites(boolean sortByAlphabet) {
            List<Word> favorites = new ArrayList<>();
            SQLiteDatabase db = this.getReadableDatabase();
            
            try {
                // 构建排序条件
                String orderBy = sortByAlphabet ? COLUMN_WORD + " ASC" : COLUMN_FAVORITE_TIME + " DESC";
                
                String query = "SELECT * FROM " + TABLE_FAVORITES + " ORDER BY " + orderBy;
                
                Cursor cursor = db.rawQuery(query, null);
                
                if (cursor.moveToFirst()) {
                    int wordIndex = cursor.getColumnIndex(COLUMN_WORD);
                    int phoneticIndex = cursor.getColumnIndex(COLUMN_PHONETIC);
                    int definitionIndex = cursor.getColumnIndex(COLUMN_DEFINITION);
                    int translationIndex = cursor.getColumnIndex(COLUMN_TRANSLATION);
                    
                    do {
                        Word word = new Word();
                        word.setWord(cursor.getString(wordIndex));
                        word.setPhonetic(cursor.getString(phoneticIndex));
                        word.setDefinition(cursor.getString(definitionIndex));
                        word.setTranslation(cursor.getString(translationIndex));
                        word.setFavorite(true); // 设置为收藏状态
                        
                        favorites.add(word);
                    } while (cursor.moveToNext());
                }
                
                cursor.close();
            } catch (Exception e) {
                Log.e(TAG, "Error getting favorite words", e);
            }
            
            return favorites;
        }

        /**
         * 获取所有收藏单词的字符串列表
         */
        public List<String> getAllFavoriteWordStrings() {
            List<String> result = new ArrayList<>();
            SQLiteDatabase db = getReadableDatabase();
            
            try {
                Cursor cursor = db.query(
                        TABLE_FAVORITES,
                        new String[]{COLUMN_WORD},
                        null,
                        null,
                        null,
                        null,
                        null
                );
                
                if (cursor != null) {
                    while (cursor.moveToNext()) {
                        String word = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_WORD));
                        result.add(word);
                    }
                    cursor.close();
                }
            } catch (Exception e) {
                Log.e(TAG, "获取收藏单词列表出错", e);
            }
            
            return result;
        }
    }
} 